﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using PMS.ENT;
using PMS.Utilities;
using System.Data;
using System.Data.SqlClient;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;

namespace PMS.DAL
{
    public class ProjectDAL
    {
        #region Common
        private static ProjectENT CreateProjectFromReader(IDataReader reader)
        {
            ProjectENT item = new ProjectENT();
            try
            {

                if (!reader.IsDBNull(reader.GetOrdinal(ProjectColumns.ProjectID.ToString()))) item.ProjectID = Utility.IntParse(reader[ProjectColumns.ProjectID.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(ProjectColumns.ProjectName.ToString()))) item.ProjectName = Utility.StringParse(reader[ProjectColumns.ProjectName.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(ProjectColumns.StartDate.ToString()))) item.StartDate = Utility.DateTimeParse(reader[ProjectColumns.StartDate.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(ProjectColumns.EndDate.ToString()))) item.EndDate = Utility.DateTimeParse(reader[ProjectColumns.EndDate.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(ProjectColumns.OwnerID.ToString()))) item.OwnerID = Utility.IntParse(reader[ProjectColumns.OwnerID.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(ProjectColumns.SubjectID.ToString()))) item.SubjectID = Utility.IntParse(reader[ProjectColumns.SubjectID.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(ProjectColumns.NumberOfGroup.ToString()))) item.NumberOfGroup = Utility.IntParse(reader[ProjectColumns.NumberOfGroup.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(ProjectColumns.AttemptGroup.ToString()))) item.AttemptGroup = Utility.IntParse(reader[ProjectColumns.AttemptGroup.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(ProjectColumns.Status.ToString()))) item.Status = Utility.StringParse(reader[ProjectColumns.Status.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(ProjectColumns.Description.ToString()))) item.Description = Utility.StringParse(reader[ProjectColumns.Description.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(ProjectColumns.MaxmemOfGroup.ToString()))) item.MaxmemOfGroup = Utility.IntParse(reader[ProjectColumns.MaxmemOfGroup.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(ProjectColumns.CreatedBy.ToString()))) item.CreatedBy = Utility.IntParse(reader[ProjectColumns.CreatedBy.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(ProjectColumns.UpdateBy.ToString()))) item.UpdateBy = Utility.IntParse(reader[ProjectColumns.UpdateBy.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(ProjectColumns.CreatedDate.ToString()))) item.CreatedDate = Utility.DateTimeParse(reader[ProjectColumns.CreatedDate.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(ProjectColumns.UpdateDate.ToString()))) item.UpdateDate = Utility.DateTimeParse(reader[ProjectColumns.UpdateDate.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(ProjectColumns.Isdeleted.ToString()))) item.Isdeleted = Utility.IntParse(reader[ProjectColumns.Isdeleted.ToString()]);

                DataTable dt = reader.GetSchemaTable();
                if (Utility.FieldExist(dt, ProjectColumns.Owner.ToString()))
                {
                    if (!reader.IsDBNull(reader.GetOrdinal(ProjectColumns.Owner.ToString()))) item.Owner = Utility.StringParse(reader[ProjectColumns.Owner.ToString()]);

                }
                if (Utility.FieldExist(dt, ProjectColumns.SubjectName.ToString()))
                {
                    if (!reader.IsDBNull(reader.GetOrdinal(ProjectColumns.SubjectName.ToString()))) item.SubjectName = Utility.StringParse(reader[ProjectColumns.SubjectName.ToString()]);

                }
                if (Utility.FieldExist(dt, ProjectColumns.DepartmentID.ToString()))
                {
                    if (!reader.IsDBNull(reader.GetOrdinal(ProjectColumns.DepartmentID.ToString()))) item.DepartmentID = Utility.IntParse(reader[ProjectColumns.DepartmentID.ToString()]);

                }
                if (Utility.FieldExist(dt, ProjectColumns.Schedule.ToString()))
                {
                    if (!reader.IsDBNull(reader.GetOrdinal(ProjectColumns.Schedule.ToString()))) item.Schedule = Utility.IntParse(reader[ProjectColumns.Schedule.ToString()]);

                }
                if (Utility.FieldExist(dt, ProjectColumns.NGroup.ToString()))
                {
                    if (!reader.IsDBNull(reader.GetOrdinal(ProjectColumns.NGroup.ToString()))) item.NGroup = Utility.IntParse(reader[ProjectColumns.NGroup.ToString()]);

                }
                if (Utility.FieldExist(dt, ProjectColumns.AGroup.ToString()))
                {
                    if (!reader.IsDBNull(reader.GetOrdinal(ProjectColumns.AGroup.ToString()))) item.AGroup = Utility.IntParse(reader[ProjectColumns.AGroup.ToString()]);

                }
            }
            catch
            {
                throw;
            }
            return item;
        }

        private static StudentProjectENT CreateStudentProjectFromReader(IDataReader reader)
        {
            StudentProjectENT item = new StudentProjectENT();
            try
            {

                if (!reader.IsDBNull(reader.GetOrdinal(StudentProjectColumns.ProjectID.ToString()))) item.ProjectID = Utility.IntParse(reader[StudentProjectColumns.ProjectID.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(StudentProjectColumns.ProjectName.ToString()))) item.ProjectName = Utility.StringParse(reader[StudentProjectColumns.ProjectName.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(StudentProjectColumns.TeacherID.ToString()))) item.TeacherID = Utility.IntParse(reader[StudentProjectColumns.TeacherID.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(StudentProjectColumns.TeacherName.ToString()))) item.TeacherName = Utility.StringParse(reader[StudentProjectColumns.TeacherName.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(StudentProjectColumns.GroupID.ToString()))) item.GroupID = Utility.IntParse(reader[StudentProjectColumns.GroupID.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(StudentProjectColumns.GroupName.ToString()))) item.GroupName = Utility.StringParse(reader[StudentProjectColumns.GroupName.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(StudentProjectColumns.StudentID.ToString()))) item.StudentID = Utility.IntParse(reader[StudentProjectColumns.StudentID.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(StudentProjectColumns.StudentName.ToString()))) item.StudentName = Utility.StringParse(reader[StudentProjectColumns.StudentName.ToString()]);

                DataTable dt = reader.GetSchemaTable();
                if (Utility.FieldExist(dt, StudentProjectColumns.ScheduleID.ToString()))
                {
                    if (!reader.IsDBNull(reader.GetOrdinal(StudentProjectColumns.ScheduleID.ToString()))) item.ScheduleID = Utility.IntParse(reader[StudentProjectColumns.ScheduleID.ToString()]);

                }
                if (Utility.FieldExist(dt, StudentProjectColumns.ScheduleName.ToString()))
                {
                    if (!reader.IsDBNull(reader.GetOrdinal(StudentProjectColumns.ScheduleName.ToString()))) item.ScheduleName = Utility.StringParse(reader[StudentProjectColumns.ScheduleName.ToString()]);

                }
                if (Utility.FieldExist(dt, StudentProjectColumns.Mark.ToString()))
                {
                    if (!reader.IsDBNull(reader.GetOrdinal(StudentProjectColumns.Mark.ToString()))) item.Mark = Utility.DecimalParse(reader[StudentProjectColumns.Mark.ToString()]);

                }

            }
            catch
            {
                throw;
            }
            return item;
        }
        #endregion

        #region Get
        public static ProjectENT GetProjectByID(int projectID)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procProjectGetByID.ToString());
                database.AddInParameter(dbCommand, "@ProjectID", DbType.Int32, projectID);

                ProjectENT project = new ProjectENT();

                using (IDataReader reader = database.ExecuteReader(dbCommand))
                {
                    if (reader.Read())
                    {
                        project = CreateProjectFromReader(reader);
                    }
                    reader.Close();
                }

                return project;
            }
            catch (SqlException)
            {
                throw new DataAccessException("BLM_ERR_GetProjectByID");
            }
        }

        public static ProjectENT GetProjectBySemesterID(int projectID,int semesterID)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procProjectGetBySemesterID.ToString());
                database.AddInParameter(dbCommand, "@ProjectID", DbType.Int32, projectID);
                database.AddInParameter(dbCommand, "@SemesterID", DbType.Int32, semesterID);
                ProjectENT project = new ProjectENT();

                using (IDataReader reader = database.ExecuteReader(dbCommand))
                {
                    if (reader.Read())
                    {
                        project = CreateProjectFromReader(reader);
                    }
                    reader.Close();
                }

                return project;
            }
            catch (SqlException)
            {
                throw new DataAccessException("BLM_ERR_GetProjectBySemesterID");
            }
        }

        public static ProjectENT GetProjectByScheduleGroupID(int scheduleGroupID)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procProjectGetByScheduleGroupID.ToString());
                database.AddInParameter(dbCommand, "@ScheduleGroupID", DbType.Int32, scheduleGroupID);

                ProjectENT project = new ProjectENT();

                using (IDataReader reader = database.ExecuteReader(dbCommand))
                {
                    if (reader.Read())
                    {
                        project = CreateProjectFromReader(reader);
                    }
                    reader.Close();
                }

                return project;
            }
            catch (SqlException)
            {
                throw new DataAccessException("BLM_ERR_GetProjectByID");
            }
        }

        public static ProjectCollection GetProjectListBySubjectID(int subjectID)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procProjectGetListBySubjectID.ToString());

                database.AddInParameter(dbCommand, "@SubjectID", DbType.Int32, subjectID);

                ProjectCollection listProject = new ProjectCollection();

                using (IDataReader reader = database.ExecuteReader(dbCommand))
                {
                    while (reader.Read())
                    {
                        listProject.Add(CreateProjectFromReader(reader));
                    }
                    reader.Close();
                }

                return listProject;
            }
            catch (SqlException)
            {
                throw new DataAccessException("BLM_ERR_GetProjectListForCMB");
            }
        }

        public static ProjectCollection GetProjectList(string projectName, int semesterID, int userID, int departmentID, int subjectID, string status, string orderBy, string orderDirection, int page, int pageSize, out int totalRecords)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procProjectGetList.ToString());
                ProjectCollection listProject = new ProjectCollection();

                database.AddInParameter(dbCommand, "@ProjectName", DbType.String, projectName);
                database.AddInParameter(dbCommand, "@UserID", DbType.Int32, userID);
                database.AddInParameter(dbCommand, "@SemesterID", DbType.Int32, semesterID);
                database.AddInParameter(dbCommand, "@DepartmentID", DbType.Int32, departmentID);
                database.AddInParameter(dbCommand, "@SubjectID", DbType.Int32, subjectID);
                database.AddInParameter(dbCommand, "@Status", DbType.String, status);
                database.AddInParameter(dbCommand, "@OrderBy", DbType.AnsiString, orderBy);
                database.AddInParameter(dbCommand, "@OrderDirection", DbType.AnsiString, orderDirection);
                database.AddInParameter(dbCommand, "@Page", DbType.Int32, page);
                database.AddInParameter(dbCommand, "@PageSize", DbType.Int32, pageSize);
                database.AddOutParameter(dbCommand, "@TotalRecords", DbType.Int32, 0);

                using (IDataReader reader = database.ExecuteReader(dbCommand))
                {
                    while (reader.Read())
                    {
                        listProject.Add(CreateProjectFromReader(reader));
                    }
                    reader.Close();
                }

                totalRecords = (int)database.GetParameterValue(dbCommand, "@TotalRecords");

                return listProject;
            }
            catch (SqlException)
            {
                throw new DataAccessException("BLM_ERR_GetProjectList");
            }
        }

        public static ProjectCollection GetProjectList(string projectName,  int departmentID, int subjectID,  string orderBy, string orderDirection, int page, int pageSize, out int totalRecords)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procProjectGetListGroupFromReference.ToString());
                ProjectCollection listProject = new ProjectCollection();

                database.AddInParameter(dbCommand, "@ProjectName", DbType.String, projectName);
                database.AddInParameter(dbCommand, "@DepartmentID", DbType.Int32, departmentID);
                database.AddInParameter(dbCommand, "@SubjectID", DbType.Int32, subjectID);
                database.AddInParameter(dbCommand, "@OrderBy", DbType.AnsiString, orderBy);
                database.AddInParameter(dbCommand, "@OrderDirection", DbType.AnsiString, orderDirection);
                database.AddInParameter(dbCommand, "@Page", DbType.Int32, page);
                database.AddInParameter(dbCommand, "@PageSize", DbType.Int32, pageSize);
                database.AddOutParameter(dbCommand, "@TotalRecords", DbType.Int32, 0);

                using (IDataReader reader = database.ExecuteReader(dbCommand))
                {
                    while (reader.Read())
                    {
                        listProject.Add(CreateProjectFromReader(reader));
                    }
                    reader.Close();
                }

                totalRecords = (int)database.GetParameterValue(dbCommand, "@TotalRecords");

                return listProject;
            }
            catch (SqlException)
            {
                throw new DataAccessException("BLM_ERR_GetProjectList");
            }
        }


        public static ProjectCollection GetProjectListForCMB(int userID)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procProjectGetListForCMB.ToString());

                ProjectCollection listProject = new ProjectCollection();
                database.AddInParameter(dbCommand, "@UserID", DbType.String, userID);

                using (IDataReader reader = database.ExecuteReader(dbCommand))
                {
                    while (reader.Read())
                    {
                        listProject.Add(CreateProjectFromReader(reader));
                    }
                    reader.Close();
                }

                return listProject;
            }
            catch (SqlException)
            {
                throw new DataAccessException("BLM_ERR_GetProjectListForCMB");
            }
        }

        public static ProjectCollection GetProjectListByOwnerAndStatus(int userID, string status, string projectID)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procProjectGetListByOwnerAndStatus.ToString());

                database.AddInParameter(dbCommand, "@Status", DbType.String, status);
                database.AddInParameter(dbCommand, "@ProjectID", DbType.String, projectID);
                database.AddInParameter(dbCommand, "@UserID", DbType.Int32, userID);

                ProjectCollection listProject = new ProjectCollection();

                using (IDataReader reader = database.ExecuteReader(dbCommand))
                {
                    while (reader.Read())
                    {
                        listProject.Add(CreateProjectFromReader(reader));
                    }
                    reader.Close();
                }

                return listProject;
            }
            catch (SqlException)
            {
                throw new DataAccessException("BLM_ERR_GetProjectListForCMB");
            }
        }

        public static StudentProjectCollection GetStudentProjectList(int semesterID, int departmentID, int subjectID)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procStudentProjectReport.ToString());
                database.AddInParameter(dbCommand, "@SemesterID", DbType.Int32, semesterID);
                database.AddInParameter(dbCommand, "@DepartmentID", DbType.Int32, departmentID);
                database.AddInParameter(dbCommand, "@SubjectID", DbType.Int32, subjectID);

                StudentProjectCollection listProject = new StudentProjectCollection();

                using (IDataReader reader = database.ExecuteReader(dbCommand))
                {
                    while (reader.Read())
                    {
                        listProject.Add(CreateStudentProjectFromReader(reader));
                    }
                    reader.Close();
                }

                return listProject;
            }
            catch (SqlException)
            {
                throw new DataAccessException("BLM_ERR_GetProjectListForCMB");
            }
        }

        public static StudentProjectCollection GetStudentList(int semesterID, int departmentID, int subjectID)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procProgressListReport.ToString());
                database.AddInParameter(dbCommand, "@SemesterID", DbType.Int32, semesterID);
                database.AddInParameter(dbCommand, "@DepartmentID", DbType.Int32, departmentID);
                database.AddInParameter(dbCommand, "@SubjectID", DbType.Int32, subjectID);

                StudentProjectCollection listProject = new StudentProjectCollection();

                using (IDataReader reader = database.ExecuteReader(dbCommand))
                {
                    while (reader.Read())
                    {
                        listProject.Add(CreateStudentProjectFromReader(reader));
                    }
                    reader.Close();
                }

                return listProject;
            }
            catch (SqlException)
            {
                throw new DataAccessException("BLM_ERR_GetProjectListForCMB");
            }
        }

        public static ProjectCollection GetProjectList(int semesterID, int departmentID, int subjectID)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procProjectRpt.ToString());
                ProjectCollection listProject = new ProjectCollection();
                database.AddInParameter(dbCommand, "@SemesterID", DbType.Int32, semesterID);
                database.AddInParameter(dbCommand, "@DepartmentID", DbType.Int32, departmentID);
                database.AddInParameter(dbCommand, "@SubjectID", DbType.Int32, subjectID);
                using (IDataReader reader = database.ExecuteReader(dbCommand))
                {
                    while (reader.Read())
                    {
                        listProject.Add(CreateProjectFromReader(reader));
                    }
                    reader.Close();
                }


                return listProject;
            }
            catch (SqlException)
            {
                throw new DataAccessException("BLM_ERR_GetProjectList");
            }
        }

        #endregion

        #region Insert, Update, Delete

        public static int InsertProject(ProjectENT project)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procProjectInsert.ToString());
                int projectID = 0;
                database.AddOutParameter(dbCommand, "@ProjectID", DbType.Int32, 0);
                database.AddInParameter(dbCommand, "@ProjectName", DbType.String, project.ProjectName);
                database.AddInParameter(dbCommand, "@StartDate", DbType.DateTime, project.StartDate);
                database.AddInParameter(dbCommand, "@EndDate", DbType.DateTime, project.EndDate);
                database.AddInParameter(dbCommand, "@OwnerID", DbType.Int32, project.OwnerID);
                database.AddInParameter(dbCommand, "@SubjectID", DbType.Int32, project.SubjectID);
                database.AddInParameter(dbCommand, "@NumberOfGroup", DbType.Int32, project.NumberOfGroup);
                database.AddInParameter(dbCommand, "@AttemptGroup", DbType.Int32, project.AttemptGroup);
                database.AddInParameter(dbCommand, "@Status", DbType.String, project.Status);
                database.AddInParameter(dbCommand, "@Description", DbType.String, project.Description);
                database.AddInParameter(dbCommand, "@MaxmemOfGroup", DbType.Int32, project.MaxmemOfGroup);
                database.AddInParameter(dbCommand, "@CreatedBy", DbType.Int32, project.CreatedBy);
                database.AddInParameter(dbCommand, "@UpdateBy", DbType.Int32, project.UpdateBy);
                database.AddInParameter(dbCommand, "@Isdeleted", DbType.Int32, project.Isdeleted);

                database.ExecuteNonQuery(dbCommand);
                projectID = Utility.IntParse(database.GetParameterValue(dbCommand, "@ProjectID"));
                return projectID;

            }
            catch (Exception)
            {
                throw new DataAccessException("BLM_ERR_InsertProject");
            }
        }

        public static int UpdateProject(ProjectENT project)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procProjectUpdate.ToString());

                database.AddInParameter(dbCommand, "@ProjectID", DbType.Int32, project.ProjectID);
                database.AddInParameter(dbCommand, "@ProjectName", DbType.String, project.ProjectName);
                //database.AddInParameter(dbCommand, "@StartDate", DbType.DateTime, project.StartDate);
                //database.AddInParameter(dbCommand, "@EndDate", DbType.DateTime, project.EndDate);

                database.AddInParameter(dbCommand, "@StartDate", DbType.DateTime, null);
                database.AddInParameter(dbCommand, "@EndDate", DbType.DateTime, null);

                database.AddInParameter(dbCommand, "@OwnerID", DbType.Int32, project.OwnerID);
                database.AddInParameter(dbCommand, "@SubjectID", DbType.Int32, project.SubjectID);
                database.AddInParameter(dbCommand, "@NumberOfGroup", DbType.Int32, project.NumberOfGroup);
                database.AddInParameter(dbCommand, "@AttemptGroup", DbType.Int32, project.AttemptGroup);
                database.AddInParameter(dbCommand, "@Status", DbType.String, project.Status);
                database.AddInParameter(dbCommand, "@Description", DbType.String, project.Description);
                database.AddInParameter(dbCommand, "@MaxmemOfGroup", DbType.Int32, project.MaxmemOfGroup);
                database.AddInParameter(dbCommand, "@UpdateBy", DbType.Int32, project.UpdateBy);
                database.AddInParameter(dbCommand, "@Isdeleted", DbType.Int32, project.Isdeleted);

                if (database.ExecuteNonQuery(dbCommand) > 0)
                {
                    return project.ProjectID;
                }
                return -1;
            }
            catch (Exception)
            {
                throw new DataAccessException("BLM_ERR_UpdateProject");
            }
        }

        public static int DeleteProject(string projectID, int userID)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procProjectDelete.ToString());
                database.AddInParameter(dbCommand, "@ProjectID", DbType.String, projectID);
                database.AddInParameter(dbCommand, "@UserID", DbType.Int32, userID);

                if (database.ExecuteNonQuery(dbCommand) > 0)
                {
                    return 1;
                }
                return 0;
            }
            catch (Exception)
            {
                throw new DataAccessException("BLM_ERR_DeleteProject");
            }
        }

        public static int ConfirmProject(string projectID, int userID)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procProjectConfirm.ToString());
                database.AddInParameter(dbCommand, "@ProjectID", DbType.String, projectID);
                database.AddInParameter(dbCommand, "@UserID", DbType.Int32, userID);

                if (database.ExecuteNonQuery(dbCommand) > 0)
                {
                    return 1;
                }
                return 0;
            }
            catch (Exception)
            {
                throw new DataAccessException("BLM_ERR_ConfirmProject");
            }
        }

        public static int JoinProject(int projectID, int groupID, int userID, int semesterID)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procProjectJoin.ToString());
                int project_groupID = 0;
                database.AddOutParameter(dbCommand, "@ProjectGroupID", DbType.Int32, 0);
                database.AddInParameter(dbCommand, "@ProjectID", DbType.String, projectID);
                database.AddInParameter(dbCommand, "@GroupID", DbType.Int32, groupID);
                database.AddInParameter(dbCommand, "@UserID", DbType.Int32, userID);
                database.AddInParameter(dbCommand, "@SemesterID", DbType.Int32, semesterID);

                database.ExecuteNonQuery(dbCommand);
                project_groupID = Utility.IntParse(database.GetParameterValue(dbCommand, "@ProjectGroupID"));
                return project_groupID;

            }
            catch (Exception)
            {
                throw new DataAccessException("BLM_ERR_JoinProject");
            }
        }

        public static int UnJoinProject(int projectID, int groupID, int userID, int semesterID)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procProjectUnJoin.ToString());
               
                database.AddInParameter(dbCommand, "@ProjectID", DbType.String, projectID);
                database.AddInParameter(dbCommand, "@GroupID", DbType.Int32, groupID);
                database.AddInParameter(dbCommand, "@UserID", DbType.Int32, userID);
                database.AddInParameter(dbCommand, "@SemesterID", DbType.Int32, semesterID);

                if (database.ExecuteNonQuery(dbCommand) > 0)
                {
                    return 1;
                }
                
                return 0;

            }
            catch (Exception)
            {
                throw new DataAccessException("BLM_ERR_JoinProject");
            }
        }
        #endregion

        #region Validate
        public static bool CheckIsOwnAndDraft(int[] projectID, int userID)
        {
            try
            {
                if (projectID.Length == GetProjectListByOwnerAndStatus(userID, ProjectStatus.Draft.ToString(), Utility.StringParse(projectID)).Count)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception)
            {
                throw new DataAccessException("BLM_ERR_CheckIsOwnAndDraft");
            }
        }

        public static bool CheckProjectStatus(int[] projectID, string status)
        {
            try
            {
                if (projectID.Length == GetProjectListByOwnerAndStatus(0, status, Utility.StringParse(projectID)).Count)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception)
            {
                throw new DataAccessException("BLM_ERR_CheckProjectStatus");
            }
        }

        public static bool CheckIsOwn(int[] projectID, int userID)
        {
            try
            {
                if (projectID.Length == GetProjectListByOwnerAndStatus(userID, ProjectStatus.Confirm.ToString(), Utility.StringParse(projectID)).Count)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception)
            {
                throw new DataAccessException("BLM_ERR_CheckIsOwnAndDraft");
            }
        }

        public static int CheckIsJoin(int projectID, int userID, int semesterID)
        {
            try
            {
                int groupID = 0;
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procProjectCheckIsJoin.ToString());
                database.AddInParameter(dbCommand, "@ProjectID", DbType.Int32, projectID);
                database.AddInParameter(dbCommand, "@UserID", DbType.Int32, userID);
                database.AddInParameter(dbCommand, "@SemesterID", DbType.Int32, semesterID);
                database.AddOutParameter(dbCommand, "@GroupID", DbType.Int32, 0);

                using (IDataReader reader = database.ExecuteReader(dbCommand))
                {
                    groupID = Utility.IntParse(database.GetParameterValue(dbCommand, "@GroupID"));
                }

                return groupID;
            }
            catch (SqlException)
            {
                throw new DataAccessException("BLM_ERR_CheckIsJoin");
            }
        }

        #endregion

    }
}

